﻿using Domain.Entitys.Sinv;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Extention
{
    public class ExeclHelper
    {
        //public static MemoryStream Export(DataTable dtSource, string title, bool showTitle = true)
        //{
        //    using (ExcelPackage package = new ExcelPackage())
        //    {
        //        ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(title);

        //        int maxColumnCount = dtSource.Columns.Count;
        //        int curRowIndex = 0;

        //        if (showTitle == true)
        //        {
        //            curRowIndex++;
        //            //主题
        //            workSheet.Cells[curRowIndex, 1, 1, maxColumnCount].Merge = true;
        //            workSheet.Cells[curRowIndex, 1].Value = title;
        //            var headerStyle = workSheet.Workbook.Styles.CreateNamedStyle("headerStyle");
        //            headerStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            headerStyle.Style.Font.Bold = true;
        //            headerStyle.Style.Font.Size = 20;
        //            workSheet.Cells[curRowIndex, 1].StyleName = "headerStyle";

        //            curRowIndex++;
        //            //导出时间栏
        //            workSheet.Cells[curRowIndex, 1, 2, maxColumnCount].Merge = true;
        //            workSheet.Cells[curRowIndex, 1].Value = "导出时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm");
        //            workSheet.Cells[curRowIndex, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
        //        }

        //        curRowIndex++;
        //        var titleStyle = workSheet.Workbook.Styles.CreateNamedStyle("titleStyle");
        //        titleStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //        titleStyle.Style.Font.Bold = true;
        //        //标题
        //        for (var i = 0; i < maxColumnCount; i++)
        //        {
        //            DataColumn column = dtSource.Columns[i];
        //            workSheet.Cells[curRowIndex, i + 1].Value = column.ColumnName;
        //            workSheet.Cells[curRowIndex, i + 1].StyleName = "titleStyle";
        //        }
        //        workSheet.View.FreezePanes(curRowIndex, 1);//冻结标题行

        //        //内容
        //        for (var i = 0; i < dtSource.Rows.Count; i++)
        //        {
        //            curRowIndex++;
        //            for (var j = 0; j < maxColumnCount; j++)
        //            {
        //                DataColumn column = dtSource.Columns[j];
        //                var row = dtSource.Rows[i];
        //                object value = row[column];
        //                var cell = workSheet.Cells[curRowIndex, j + 1];
        //                var pType = column.DataType;
        //                pType = pType.Name == "Nullable`1" ? Nullable.GetUnderlyingType(pType) : pType;
        //                if (pType == typeof(DateTime))
        //                {
        //                    cell.Style.Numberformat.Format = "yyyy-MM-dd hh:mm";
        //                    cell.Value = Convert.ToDateTime(value);
        //                }
        //                else if (pType == typeof(int))
        //                {
        //                    cell.Value = Convert.ToInt32(value);
        //                }
        //                else if (pType == typeof(double) || pType == typeof(decimal))
        //                {
        //                    cell.Value = Convert.ToDouble(value);
        //                }
        //                else
        //                {
        //                    cell.Value = value == null ? "" : value.ToString();
        //                }
        //                workSheet.Cells[curRowIndex, j + 1].Value = row[column].ToString();
        //            }
        //        }
        //        workSheet.Cells[workSheet.Dimension.Address].Style.Font.Name = "宋体";
        //        workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();//自动填充
        //        for (var i = 1; i <= workSheet.Dimension.End.Column; i++) { workSheet.Column(i).Width = workSheet.Column(i).Width + 2; }//在填充的基础上再加2
        //        MemoryStream ms = new MemoryStream(package.GetAsByteArray());
        //        return ms;
        //    }
        //}

        public static MemoryStream Export(
            string[] headers,          // 标题集合
            IEnumerable<object[]> rows, // 数据集合（每行是object数组）
            string title,
            //List<Sinv_Customer> sinv_Customers,
            bool showTitle = true,
            Type[] columnTypes = null) // 可选列类型
        {
            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(title);
                int maxColumnCount = headers.Length;
                int curRowIndex = 0;

                //1.标题区域（可选）
                if (showTitle)
                {
                    curRowIndex++;
                    workSheet.Cells[curRowIndex, 1, 1, maxColumnCount].Merge = true;
                    workSheet.Cells[curRowIndex, 1].Value = title;
                    workSheet.Cells[curRowIndex, 1].StyleName = CreateTitleStyle(workSheet);

                    curRowIndex++;
                    workSheet.Cells[curRowIndex, 1, 2, maxColumnCount].Merge = true;
                    workSheet.Cells[curRowIndex, 1].Value = "导出时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm");
                    workSheet.Cells[curRowIndex, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                }

                //2.列标题
                curRowIndex++;
                for (int i = 0; i < maxColumnCount; i++)
                {
                    workSheet.Cells[curRowIndex, i + 1].Value = headers[i];
                    workSheet.Cells[curRowIndex, i + 1].StyleName = CreateTitleStyle(workSheet);
                }
                workSheet.View.FreezePanes(curRowIndex, 1); // 冻结标题行

                //3.数据行
                int rowIndex = 0;
                foreach (var rowData in rows)
                {
                    curRowIndex++;
                    for (int j = 0; j < maxColumnCount; j++)
                    {
                        var cell = workSheet.Cells[curRowIndex, j + 1];
                        object value = (j < rowData.Length) ? rowData[j] : null;

                        //数据类型处理（若有类型信息）
                        if (columnTypes != null && j < columnTypes.Length)
                        {
                            var colType = columnTypes[j];
                            colType = Nullable.GetUnderlyingType(colType) ?? colType;

                            if (colType == typeof(DateTime))
                            {
                                cell.Style.Numberformat.Format = "yyyy-MM-dd hh:mm";
                                cell.Value = Convert.ToDateTime(value);
                            }
                            else if (colType == typeof(int))
                            {
                                cell.Value = Convert.ToInt32(value);
                            }
                            else if (colType == typeof(double) || colType == typeof(decimal))
                            {
                                cell.Value = Convert.ToDouble(value);
                            }
                            else
                            {
                                cell.Value = value?.ToString() ?? "";
                            }
                        }
                        else // 无类型信息时按字符串处理
                        {
                            cell.Value = value?.ToString() ?? "";
                        }
                    }
                    rowIndex++;
                }

                //4.样式优化
                workSheet.Cells[workSheet.Dimension.Address].Style.Font.Name = "宋体";
                workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
                for (int i = 1; i <= maxColumnCount; i++)
                {
                    workSheet.Column(i).Width += 2;
                }

                return new MemoryStream(package.GetAsByteArray());
            }
        }

        //辅助方法：创建标题样式
        private static string CreateTitleStyle(ExcelWorksheet ws)
        {
            var style = ws.Workbook.Styles.CreateNamedStyle("titleStyle");
            style.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            style.Style.Font.Bold = true;
            return "titleStyle";
        }

        /// <summary>
        /// 生成excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dtSource">数据源</param>
        /// <param name="columns">导出字段表头合集</param>
        /// <param name="title">标题(Sheet名)</param>
        /// <param name="showTitle">是否显示标题</param>
        /// <returns></returns>
        //public static byte[] Export<T>(IList<T> dtSource, ExportColumnCollective columns, string title, bool showTitle = true)
        //{
        //    using (ExcelPackage package = new ExcelPackage())
        //    {
        //        ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(title);

        //        int maxColumnCount = columns.ExportColumnList.Count;
        //        int curRowIndex = 0;

        //        //Excel标题
        //        if (showTitle == true)
        //        {
        //            curRowIndex++;
        //            workSheet.Cells[curRowIndex, 1, 1, maxColumnCount].Merge = true;
        //            workSheet.Cells[curRowIndex, 1].Value = title;
        //            var headerStyle = workSheet.Workbook.Styles.CreateNamedStyle("headerStyle");
        //            headerStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            headerStyle.Style.Font.Bold = true;
        //            headerStyle.Style.Font.Size = 20;
        //            workSheet.Cells[curRowIndex, 1].StyleName = "headerStyle";

        //            curRowIndex++;
        //            //导出时间
        //            workSheet.Cells[curRowIndex, 1, 2, maxColumnCount].Merge = true;
        //            workSheet.Cells[curRowIndex, 1].Value = "导出时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm");
        //            workSheet.Cells[curRowIndex, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
        //        }

        //        //数据表格标题(列名)
        //        for (int i = 0, rowCount = columns.HeaderExportColumnList.Count; i < rowCount; i++)
        //        {
        //            curRowIndex++;
        //            workSheet.Cells[curRowIndex, 1, curRowIndex, maxColumnCount].Style.Font.Bold = true;
        //            var curColSpan = 1;
        //            for (int j = 0, colCount = columns.HeaderExportColumnList[i].Count; j < colCount; j++)
        //            {
        //                var colColumn = columns.HeaderExportColumnList[i][j];
        //                var colSpan = FindSpaceCol(workSheet, curRowIndex, curColSpan);
        //                if (j == 0) curColSpan = colSpan;
        //                var toColSpan = colSpan + colColumn.ColSpan;
        //                var cell = workSheet.Cells[curRowIndex, colSpan, colColumn.RowSpan + curRowIndex, toColSpan];
        //                cell.Merge = true;
        //                cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
        //                cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //                workSheet.Cells[curRowIndex, colSpan].Value = colColumn.Title;
        //                curColSpan += colColumn.ColSpan;
        //            }
        //        }
        //        workSheet.View.FreezePanes(curRowIndex + 1, 1);//冻结标题行

        //        Type type = typeof(T);
        //        PropertyInfo[] propertyInfos = type.GetProperties();
        //        if (propertyInfos.Count() == 0 && dtSource.Count > 0) propertyInfos = dtSource[0].GetType().GetProperties();

        //        //数据行
        //        for (int i = 0, sourceCount = dtSource.Count(); i < sourceCount; i++)
        //        {
        //            curRowIndex++;
        //            for (var j = 0; j < maxColumnCount; j++)
        //            {
        //                var column = columns.ExportColumnList[j];
        //                var cell = workSheet.Cells[curRowIndex, j + 1];
        //                foreach (var propertyInfo in propertyInfos)
        //                {
        //                    if (column.Field == propertyInfo.Name)
        //                    {
        //                        object value = propertyInfo.GetValue(dtSource[i]);
        //                        var pType = propertyInfo.PropertyType;
        //                        pType = pType.Name == "Nullable`1" ? Nullable.GetUnderlyingType(pType) : pType;
        //                        if (pType == typeof(DateTime))
        //                        {
        //                            cell.Style.Numberformat.Format = "yyyy-MM-dd hh:mm";
        //                            cell.Value = Convert.ToDateTime(value);
        //                        }
        //                        else if (pType == typeof(int))
        //                        {
        //                            cell.Style.Numberformat.Format = "#0";
        //                            cell.Value = Convert.ToInt32(value);
        //                        }
        //                        else if (pType == typeof(double) || pType == typeof(decimal))
        //                        {
        //                            if (column.Precision != null) cell.Style.Numberformat.Format = "#,##0.00";//保留两位小数

        //                            cell.Value = Convert.ToDouble(value);
        //                        }
        //                        else
        //                        {
        //                            cell.Value = value == null ? "" : value.ToString();
        //                        }
        //                    }
        //                }
        //            }
        //        }
        //        workSheet.Cells[workSheet.Dimension.Address].Style.Font.Name = "宋体";
        //        workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();//自动填充
        //        for (var i = 1; i <= workSheet.Dimension.End.Column; i++) { workSheet.Column(i).Width = workSheet.Column(i).Width + 2; }//在填充的基础上再加2

        //        return package.GetAsByteArray();
        //    }
        //}

        //private static int FindSpaceCol(ExcelWorksheet workSheet, int row, int col)
        //{
        //    if (workSheet.Cells[row, col].Merge)
        //    {
        //        return FindSpaceCol(workSheet, row, col + 1);
        //    }
        //    return col;
        //}

    }
}
